﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Windows.Forms;
using NutriStat.Data;
using NutriStat.Statistics;
using NutriStat;

namespace NutriStat
{
    /// <summary>
    /// Patient object; acts as the interface between the GUI and the database.
    /// </summary>
    public class NutriPatient : PatientBase
    {
        #region Data
        #region Protected Data
        #endregion Protected Data

        #region Private Data
        private NutriStat.Data.IDbDriver db;
        private DataTable dt;
        private bool datasetLoaded;
        private string connString;
        private bool validNutriStatDatabase;
        #endregion Private Data

        #region Internal Data
        internal IVisit visit;
        #endregion Internal Data
        #endregion Data

        #region Constructors
        /// <summary>
        /// Basic constructor
        /// </summary>
        public NutriPatient()
        {
            db = new AccessDatabase();
            dt = new DataTable();

            visit = new NutriVisit("M");
            LastName = NutriStringConstants.Empty;
            FirstName = NutriStringConstants.Empty;
            Address = NutriStringConstants.Empty;
            Mother = NutriStringConstants.Empty;
            Father = NutriStringConstants.Empty;
        }

        /// <summary>
        /// Constructor used for populating values
        /// </summary>
        /// <param name="birthDate">The birth date of the patient.</param>
        /// <param name="firstName">The patient's first name.</param>
        /// <param name="id">The ID number of the patient.</param>
        /// <param name="lastName">The patient's last name.</param>
        /// <param name="sex">The patient's gender.</param>
        public NutriPatient(int id, string lastName, string firstName, DateTime birthDate, string sex)
        {
            #region Input Validation
            // Input validation
            if (id < 0)
            {
                throw new Exception("Parameter out of bounds in NutriPatient constructor. Value: " + id.ToString());
            }

            if (sex != "M" && sex != "F")
            {
                throw new Exception("Parameter out of bounds in NutriPatient constructor. Value: " + sex);
            }

            if (lastName.Length > 256)
            {
                throw new Exception("Parameter out of bounds in NutriPatient constructor. Value: " + lastName.Length.ToString());
            }

            if (firstName.Length > 256)
            {
                throw new Exception("Parameter out of bounds in NutriPatient constructor. Value: " + firstName.Length.ToString());
            }

            if(birthDate.Date < DateTime.MinValue || birthDate.Date > DateTime.MaxValue)
            {
                throw new Exception("Parameter out of bounds in NutriPatient constructor. Value: " + birthDate.Date.ToShortDateString());
            }
            #endregion

            db = new AccessDatabase();
            dt = new DataTable();

            visit = new NutriVisit(sex);
            LastName = lastName;
            FirstName = firstName;
            BirthDate = birthDate;
            Address = NutriStringConstants.Empty;
            Mother = NutriStringConstants.Empty;
            Father = NutriStringConstants.Empty;
        }

        /// <summary>
        /// The destructor
        /// </summary>
        ~NutriPatient()
        {
            dt.Dispose();
            db.Dispose();
        }
        #endregion Constructors

        #region Properties
        #region Public Properties
        /// <summary>
        /// Gets/sets whether this is a valid NutriStat database. Necessary for the import and
        /// add statistics routines to properly run.
        /// </summary>
        public bool ValidNutriStatDatabase
        {
            get
            {
                return validNutriStatDatabase;
            }
            set
            {
                validNutriStatDatabase = value;
            }
        }
        
        /// <summary>
        /// Gets/sets the data table being used
        /// </summary>
        public IVisit Visit
        {
            get
            {
                return visit;
            }
        }

        /// <summary>
        /// Gets/sets the database being used
        /// </summary>
        public IDbDriver Database
        {
            get
            {
                return db;
            }
            set
            {
                db = value;
            }
        }

        /// <summary>
        /// Gets/sets the data table being used
        /// </summary>
        public DataTable Table
        {
            get
            {
                return dt;
            }
            set
            {
                dt = value;
            }
        }

        /// <summary>
        /// Gets/sets whether or not a database has been loaded.
        /// </summary>
        public bool DatabaseLoaded
        {
            get
            {
                return datasetLoaded;
            }
            set
            {
                datasetLoaded = value;
            }
        }

        /// <summary>
        /// Gets/sets the connection string. Mostly used for display purposes.
        /// </summary>
        public string ConnectionString
        {
            get
            {
                return connString;
            }
            set
            {
                connString = value;
            }
        }
        #endregion Public Properties

        #region Protected Properties
        #endregion Protected Properties

        #region Private Properties
        #endregion Private Properties
        #endregion Properties

        #region Methods
        #region Public Methods
        /// <summary>
        /// Gets the type of database that has been loaded
        /// </summary>        
        /// <returns>string</returns>
        internal string GetDatabaseType()
        {
            string databaseType = "";
            databaseType = db.IdentifyDatabase();
            return databaseType;
        }

        /// <summary>
        /// Gets the number of records in the data table
        /// </summary>        
        /// <returns>int</returns>
        internal int GetRecordCount()
        {
            return dt.Rows.Count;
        }

        /// <summary>
        /// Gets a series of x/y data points to use in NutriChart
        /// </summary>                
        /// <param name="growthReference">The growth reference type to use, e.g. CDC 2000, WHO 1978, etc.</param>
        /// <param name="measurementOption">The measurement option to use, e.g. Body mass index, weight-for-height, etc.</param>
        /// <param name="type">Use "P" for percentiles and "Z" for z-scores.</param>
        /// <returns>string</returns>
        internal string GetDataPointsForCharting(MeasurementOption measurementOption, string growthReference, string type)
        {
#region Input Validation
            if (!(growthReference.Equals(GrowthReferenceTypes.CDC2000) ||
                growthReference.Equals(GrowthReferenceTypes.WHO1978) ||
                growthReference.Equals(GrowthReferenceTypes.WHO2006) ||
                growthReference.Equals(GrowthReferenceTypes.WHO2007)
                ))
            {
                throw new Exception("Invalid growth reference type.");
            }

            if(!(type.Equals("P") || type.Equals("Z"))) 
            {
                throw new Exception("Invalid type specified. Please use Z or P for z-scores and percentiles, respectively.");
            }
#endregion Input Validation

            string points = "";
            
            Query selectQuery = db.CreateQuery("select * from " + dt.TableName + " where fldIdNo = " + this.Id.ToString());
            int count = 0;
            IDataReader reader = db.ExecuteReader(selectQuery);
            while (reader.Read())
            {
                string age = string.Empty;
                string measurement = string.Empty;
                string score = string.Empty;

                // CDC 2000 reference
                if (growthReference.Equals(GrowthReferenceTypes.CDC2000))
                {
                    CDC2000GrowthReference CDC = new CDC2000GrowthReference();
                    double cutoffUpper = 260;
                    double cutoffLower = 0;

                    if (measurementOption.Axis.Equals("age"))
                    {
                        age = reader["fldAge"].ToString();
                    }
                    else
                    {
                        age = reader[NutriStringConstants.ColumnHeightMetric].ToString();
                    }

                    if (measurementOption.Abbreviation.Equals(MeasurementOptions.BMI.Abbreviation))
                    {
                        measurement = reader["fldBMI"].ToString();
                        cutoffUpper = CDC.BMIUpper;
                        cutoffLower = CDC.BMILower;
                        if (type.Equals("P"))
                        {
                            score = reader["fldCDCBMIC"].ToString();                            
                        }
                        else
                        {
                            score = reader["fldCDCBMIZ"].ToString();
                        }
                    }

                    else if (measurementOption.Abbreviation.Equals(MeasurementOptions.WA.Abbreviation))                     
                    {
                        measurement = reader[NutriStringConstants.ColumnWeightMetric].ToString();
                        cutoffLower = CDC.WeightAgeLower;
                        cutoffUpper = CDC.WeightAgeUpper;                        
                        if (type.Equals("P"))
                        {
                            score = reader["fldCDCWAC"].ToString();
                        }
                        else
                        {
                            score = reader["fldCDCWAZ"].ToString();
                        }
                    }
                    else if (measurementOption.Abbreviation.Equals(MeasurementOptions.WH.Abbreviation)) 
                    {
                        measurement = reader[NutriStringConstants.ColumnWeightMetric].ToString();
                        cutoffLower = CDC.WeightHeightLower;
                        cutoffUpper = CDC.WeightHeightUpper;
                        if (type.Equals("P"))
                        {
                            score = reader["fldCDCWHC"].ToString();
                        }
                        else
                        {
                            score = reader["fldCDCWHZ"].ToString();
                        }
                    }
                    else if (measurementOption.Abbreviation.Equals(MeasurementOptions.WL.Abbreviation))
                    {
                        measurement = reader[NutriStringConstants.ColumnWeightMetric].ToString();
                        cutoffLower = CDC.WeightLengthLower;
                        cutoffUpper = CDC.WeightLengthUpper;
                        if (type.Equals("P"))
                        {
                            score = reader["fldCDCWHC"].ToString();
                        }
                        else
                        {
                            score = reader["fldCDCWHZ"].ToString();
                        }
                    }
                    else if (measurementOption.Abbreviation.Equals(MeasurementOptions.HA.Abbreviation))                    
                    {
                        measurement = reader[NutriStringConstants.ColumnHeightMetric].ToString();
                        cutoffLower = CDC.HeightAgeLower;
                        cutoffUpper = CDC.HeightAgeUpper;
                        if (type.Equals("P"))
                        {
                            score = reader["fldCDCHAC"].ToString();
                        }
                        else
                        {
                            score = reader["fldCDCHAZ"].ToString();
                        }
                    }
                    else if (measurementOption.Abbreviation.Equals(MeasurementOptions.LA.Abbreviation))
                    {
                        measurement = reader[NutriStringConstants.ColumnHeightMetric].ToString();
                        cutoffLower = CDC.LengthAgeLower;
                        cutoffUpper = CDC.LengthAgeUpper;
                        if (type.Equals("P"))
                        {
                            score = reader["fldCDCHAC"].ToString();
                        }
                        else
                        {
                            score = reader["fldCDCHAZ"].ToString();
                        }
                    }
                    else if (measurementOption.Abbreviation.Equals(MeasurementOptions.HCA.Abbreviation))
                    {
                        cutoffLower = CDC.HeadLower;
                        cutoffUpper = CDC.HeadUpper;
                        measurement = reader[NutriStringConstants.ColumnHeadMetric].ToString();
                        if (type.Equals("P"))
                        {
                            score = reader["fldCDCHCircumC"].ToString();
                        }
                        else
                        {
                            score = reader["fldCDCHCircumZ"].ToString();
                        }
                    }

                    try
                    {   
                        double numericMeasurement = Convert.ToDouble(measurement);
                        double numericAge = Convert.ToDouble(age);
                        double numericScore = Convert.ToDouble(score);
                        if (                            
                            !(numericAge > cutoffUpper || numericAge < cutoffLower)
                            &&
                            !score.Equals(string.Empty)
                            )
                        {
                            points = points + age + " " + measurement + " " + score + " ";
                        }                        
                    }
                    catch
                    {
                    }
                }

                // WHO 2006 reference
                if (growthReference.Equals(GrowthReferenceTypes.WHO2006))
                {
                    WHO2006GrowthReference WHO2006 = new WHO2006GrowthReference();
                    double cutoffUpper = 260;
                    double cutoffLower = 0;

                    if (measurementOption.Axis.Equals("age"))
                    {
                        age = reader["fldAge"].ToString();
                    }
                    else
                    {
                        age = reader[NutriStringConstants.ColumnHeightMetric].ToString();
                    }

                    if (measurementOption.Abbreviation.Equals(MeasurementOptions.BMI.Abbreviation))
                    {
                        measurement = reader["fldBMI"].ToString();
                        cutoffUpper = WHO2006.BMIUpper;
                        cutoffLower = WHO2006.BMILower;
                        if (type.Equals("P"))
                        {
                            score = reader["fldWHO2006BMIC"].ToString();
                        }
                        else
                        {
                            score = reader["fldWHO2006BMIZ"].ToString();
                        }
                    }

                    else if (measurementOption.Abbreviation.Equals(MeasurementOptions.WA.Abbreviation))
                    {
                        measurement = reader[NutriStringConstants.ColumnWeightMetric].ToString();
                        cutoffLower = WHO2006.WeightAgeLower;
                        cutoffUpper = WHO2006.WeightAgeUpper;
                        if (type.Equals("P"))
                        {
                            score = reader["fldWHO2006WAC"].ToString();
                        }
                        else
                        {
                            score = reader["fldWHO2006WAZ"].ToString();
                        }
                    }
                    else if (measurementOption.Abbreviation.Equals(MeasurementOptions.WH.Abbreviation))
                    {
                        measurement = reader[NutriStringConstants.ColumnWeightMetric].ToString();
                        cutoffLower = WHO2006.WeightHeightLower;
                        cutoffUpper = WHO2006.WeightHeightUpper;
                        if (type.Equals("P"))
                        {
                            score = reader["fldWHO2006WHC"].ToString();
                        }
                        else
                        {
                            score = reader["fldWHO2006WHZ"].ToString();
                        }
                    }
                    else if (measurementOption.Abbreviation.Equals(MeasurementOptions.WL.Abbreviation))
                    {
                        measurement = reader[NutriStringConstants.ColumnWeightMetric].ToString();
                        cutoffLower = WHO2006.WeightLengthLower;
                        cutoffUpper = WHO2006.WeightLengthUpper;
                        if (type.Equals("P"))
                        {
                            score = reader["fldWHO2006WHC"].ToString();
                        }
                        else
                        {
                            score = reader["fldWHO2006WHZ"].ToString();
                        }
                    }
                    else if (measurementOption.Abbreviation.Equals(MeasurementOptions.HA.Abbreviation))
                    {
                        measurement = reader[NutriStringConstants.ColumnHeightMetric].ToString();
                        cutoffLower = WHO2006.HeightAgeLower;
                        cutoffUpper = WHO2006.HeightAgeUpper;
                        if (type.Equals("P"))
                        {
                            score = reader["fldWHO2006HAC"].ToString();
                        }
                        else
                        {
                            score = reader["fldWHO2006HAZ"].ToString();
                        }
                    }
                    else if (measurementOption.Abbreviation.Equals(MeasurementOptions.LA.Abbreviation))
                    {
                        measurement = reader[NutriStringConstants.ColumnHeightMetric].ToString();
                        cutoffLower = WHO2006.LengthAgeLower;
                        cutoffUpper = WHO2006.LengthAgeUpper;
                        if (type.Equals("P"))
                        {
                            score = reader["fldWHO2006HAC"].ToString();
                        }
                        else
                        {
                            score = reader["fldWHO2006HAZ"].ToString();
                        }
                    }
                    else if (measurementOption.Abbreviation.Equals(MeasurementOptions.HCA.Abbreviation))
                    {
                        cutoffLower = WHO2006.HeadLower;
                        cutoffUpper = WHO2006.HeadUpper;
                        measurement = reader[NutriStringConstants.ColumnHeadMetric].ToString();
                        if (type.Equals("P"))
                        {
                            score = reader["fldWHO2006HCircumC"].ToString();
                        }
                        else
                        {
                            score = reader["fldWHO2006HCircumZ"].ToString();
                        }
                    }
                    else if (measurementOption.Abbreviation.Equals(MeasurementOptions.SSFA.Abbreviation))
                    {
                        cutoffLower = WHO2006.SSFLower;
                        cutoffUpper = WHO2006.SSFUpper;
                        measurement = reader[NutriStringConstants.ColumnSSFMetric].ToString();
                        if (type.Equals("P"))
                        {
                            score = reader["fldWHO2006SSFC"].ToString();
                        }
                        else
                        {
                            score = reader["fldWHO2006SFFZ"].ToString();
                        }
                    }
                    else if (measurementOption.Abbreviation.Equals(MeasurementOptions.TSFA.Abbreviation))
                    {
                        cutoffLower = WHO2006.TSFLower;
                        cutoffUpper = WHO2006.TSFUpper;
                        measurement = reader[NutriStringConstants.ColumnTSFMetric].ToString();
                        if (type.Equals("P"))
                        {
                            score = reader["fldWHO2006TSFC"].ToString();
                        }
                        else
                        {
                            score = reader["fldWHO2006TFFZ"].ToString();
                        }
                    }

                    try
                    {
                        double numericMeasurement = Convert.ToDouble(measurement);
                        double numericAge = Convert.ToDouble(age);
                        double numericScore = Convert.ToDouble(score);
                        if (
                            !(numericAge > cutoffUpper || numericAge < cutoffLower)
                            &&
                            !score.Equals(string.Empty)
                            )
                        {
                            points = points + age + " " + measurement + " " + score + " ";
                        }
                    }
                    catch
                    {
                    }
                }

                // WHO 1978 reference
                if (growthReference.Equals(GrowthReferenceTypes.WHO1978))
                {
                    WHO1978GrowthReference WHO1978 = new WHO1978GrowthReference();
                    double cutoffUpper = 260;
                    double cutoffLower = 0;

                    if (measurementOption.Axis.Equals("age"))
                    {
                        age = reader["fldAge"].ToString();
                    }
                    else
                    {
                        age = reader[NutriStringConstants.ColumnHeightMetric].ToString();
                    }                    

                    if (measurementOption.Abbreviation.Equals(MeasurementOptions.WA.Abbreviation))
                    {
                        measurement = reader[NutriStringConstants.ColumnWeightMetric].ToString();
                        cutoffLower = WHO1978.WeightAgeLower;
                        cutoffUpper = WHO1978.WeightAgeUpper;
                        if (type.Equals("P"))
                        {
                            score = reader["fldWHO1978WAC"].ToString();
                        }
                        else
                        {
                            score = reader["fldWHO1978WAZ"].ToString();
                        }
                    }
                    else if (measurementOption.Abbreviation.Equals(MeasurementOptions.WH.Abbreviation))
                    {
                        measurement = reader[NutriStringConstants.ColumnWeightMetric].ToString();
                        cutoffLower = WHO1978.WeightHeightLower;
                        cutoffUpper = WHO1978.WeightHeightUpper;
                        if (type.Equals("P"))
                        {
                            score = reader["fldWHO1978WHC"].ToString();
                        }
                        else
                        {
                            score = reader["fldWHO1978WHZ"].ToString();
                        }
                    }
                    else if (measurementOption.Abbreviation.Equals(MeasurementOptions.WL.Abbreviation))
                    {
                        measurement = reader[NutriStringConstants.ColumnWeightMetric].ToString();
                        cutoffLower = WHO1978.WeightLengthLower;
                        cutoffUpper = WHO1978.WeightLengthUpper;
                        if (type.Equals("P"))
                        {
                            score = reader["fldWHO1978WHC"].ToString();
                        }
                        else
                        {
                            score = reader["fldWHO1978WHZ"].ToString();
                        }
                    }
                    else if (measurementOption.Abbreviation.Equals(MeasurementOptions.HA.Abbreviation))
                    {
                        measurement = reader[NutriStringConstants.ColumnHeightMetric].ToString();
                        cutoffLower = WHO1978.HeightAgeLower;
                        cutoffUpper = WHO1978.HeightAgeUpper;
                        if (type.Equals("P"))
                        {
                            score = reader["fldWHO1978HAC"].ToString();
                        }
                        else
                        {
                            score = reader["fldWHO1978HAZ"].ToString();
                        }
                    }
                    else if (measurementOption.Abbreviation.Equals(MeasurementOptions.LA.Abbreviation))
                    {
                        measurement = reader[NutriStringConstants.ColumnHeightMetric].ToString();
                        cutoffLower = WHO1978.LengthAgeLower;
                        cutoffUpper = WHO1978.LengthAgeUpper;
                        if (type.Equals("P"))
                        {
                            score = reader["fldWHO1978HAC"].ToString();
                        }
                        else
                        {
                            score = reader["fldWHO1978HAZ"].ToString();
                        }
                    }                   

                    try
                    {
                        double numericMeasurement = Convert.ToDouble(measurement);
                        double numericAge = Convert.ToDouble(age);
                        double numericScore = Convert.ToDouble(score);
                        if (
                            !(numericAge > cutoffUpper || numericAge < cutoffLower)
                            &&
                            !score.Equals(string.Empty)
                            )
                        {
                            points = points + age + " " + measurement + " " + score + " ";
                        }
                    }
                    catch
                    {
                    }
                }

                count++;
            }

            return points;
        }

        /// <summary>
        /// Opens a database and data table
        /// </summary>
        internal bool OpenDatabase()
        {
            if (db == null || dt == null)
                return false;

            if (dt.Rows.Count <= 0)
            {
                bool success = AddNewID(true);
                if (!success) return false;
            }

            try
            {
                Query selectQuery = db.CreateQuery("select min(fldIdNo) from " + dt.TableName); // TODO: Change to max()?
                int lowId = (int)db.ExecuteScalar(selectQuery);

                LoadValuesFromDatabase(false, true, lowId, true, DateTime.Today, false);
                ValidNutriStatDatabase = true;
            }
            catch
            {
                ValidNutriStatDatabase = false;
            }

            DatabaseLoaded = true;

            return true;

        } // end OpenDatabase()

        /// <summary>
        /// Saves the data from the form to the database. If the data is not saved to the 
        /// database, then we return false so the user can be shown an error message.
        /// </summary>
        /// <param name="updateType">The type of update to perform. 0 indicates a full update; any other number indicates a date of measurement update.</param>
        /// <returns>bool</returns>
        internal bool SaveData(string updateType)
        {
            if (DatabaseLoaded == false)
                return false;

            #region Input Validation
            // Input validation
            if (updateType.Length > 2)
            {
                throw new Exception("Parameter out of bounds.");
            }
            #endregion

            string command = "";
            string userName = System.Security.Principal.WindowsIdentity.GetCurrent().Name.ToString();
            Query updateQuery;

            if (updateType.Equals("0"))
            {
                command = "update " + Table.TableName + " set ";
                command = command + "[" + NutriStringConstants.ColumnFirstName + "] = @FirstName, ";
                command = command + "[" + NutriStringConstants.ColumnLastName + "] = @Name, ";
                command = command + "[" + NutriStringConstants.ColumnSex + "] = @Sex, ";
                command = command + "[" + NutriStringConstants.ColumnBirthDate + "] = @BirthDate, ";
                command = command + "[" + NutriStringConstants.ColumnAddress + "] = @Address, ";
                command = command + "[" + NutriStringConstants.ColumnCluster + "] = @Cluster, ";
                command = command + "[" + NutriStringConstants.ColumnTeam + "] = @Team, ";
                command = command + "[" + NutriStringConstants.ColumnHousehold + "] = @Household, ";
                command = command + "[" + NutriStringConstants.ColumnMother + "] = @Mother, ";
                command = command + "[" + NutriStringConstants.ColumnFather + "] = @Father, ";
                command = command + "[fldModifiedDate] = @ModifiedDate, ";
                command = command + "[fldModifiedBy] = @ModifiedBy ";
                command = command + "where [" + NutriStringConstants.ColumnID + "] = @Id";

                updateQuery = db.CreateQuery(command);

                updateQuery.Parameters.Add(new QueryParameter("@FirstName", DbType.String, this.FirstName));
                updateQuery.Parameters.Add(new QueryParameter("@Name", DbType.String, this.LastName));
                updateQuery.Parameters.Add(new QueryParameter("@Sex", DbType.String, this.Gender));

                if (this.UseBirthDate == false)
                {
                    updateQuery.Parameters.Add(new QueryParameter("@BirthDate", DbType.Date, DBNull.Value));
                }
                else
                {
                    updateQuery.Parameters.Add(new QueryParameter("@BirthDate", DbType.Date, this.BirthDate));
                }

                updateQuery.Parameters.Add(new QueryParameter("@Address", DbType.String, this.Address));
                updateQuery.Parameters.Add(new QueryParameter("@Cluster", DbType.String, this.Cluster));
                updateQuery.Parameters.Add(new QueryParameter("@Team", DbType.String, this.Team));
                updateQuery.Parameters.Add(new QueryParameter("@Household", DbType.String, this.Household));
                updateQuery.Parameters.Add(new QueryParameter("@Mother", DbType.String, this.Mother));
                updateQuery.Parameters.Add(new QueryParameter("@Father", DbType.String, this.Father));
                updateQuery.Parameters.Add(new QueryParameter("@ModifiedDate", DbType.String, System.DateTime.Now));
                updateQuery.Parameters.Add(new QueryParameter("@ModifiedBy", DbType.String, userName));
                updateQuery.Parameters.Add(new QueryParameter("@Id", DbType.Int32, this.Id));

                db.ExecuteNonQuery(updateQuery);
            }

            command = "";
            command = "update " + Table.TableName + " set ";
            command = command + "[" + NutriStringConstants.ColumnVisitDate + "] = @CurrentDate, ";
            command = command + "[" + NutriStringConstants.ColumnFirstName + "] = @FirstName, ";
            command = command + "[" + NutriStringConstants.ColumnLastName + "] = @Name, ";
            command = command + "[" + NutriStringConstants.ColumnSex + "] = @Sex, ";
            command = command + "[" + NutriStringConstants.ColumnAge + "] = @Age, ";
            command = command + "[" + NutriStringConstants.ColumnBirthDate + "] = @BirthDate, ";
            command = command + "[" + NutriStringConstants.ColumnAddress + "] = @Address, ";
            command = command + "[" + NutriStringConstants.ColumnCluster + "] = @Cluster, ";
            command = command + "[" + NutriStringConstants.ColumnTeam + "] = @Team, ";
            command = command + "[" + NutriStringConstants.ColumnHousehold + "] = @Household, ";
            command = command + "[" + NutriStringConstants.ColumnMother + "] = @Mother, ";
            command = command + "[" + NutriStringConstants.ColumnFather + "] = @Father, ";
            command = command + "[" + NutriStringConstants.ColumnEdema + "] = @Edema, ";
            command = command + "[" + NutriStringConstants.ColumnRecumbent + "] = @Recumbent, ";
            command = command + "[" + NutriStringConstants.ColumnNotes + "] = @Notes, ";
            command = command + "[" + NutriStringConstants.ColumnHeightMetric + "] = @Cm, ";
            command = command + "[" + NutriStringConstants.ColumnHeightCustomary + "] = @Inch, ";
            command = command +
            "[" + NutriStringConstants.ColumnWeightMetric + "] = @Kilos, " +
            "[" + NutriStringConstants.ColumnWeightCustomary + "] = @Lbs, " +
            "[" + NutriStringConstants.ColumnWeightOzs + "] = @Ozs, " +
            "[" + NutriStringConstants.ColumnHeadMetric + "] = @cmHCircum, " +
            "[" + NutriStringConstants.ColumnHeadCustomary + "] = @inHCircum, " +
            "[" + NutriStringConstants.ColumnSSFMetric + "] = @mmSSF, " +
            "[" + NutriStringConstants.ColumnSSFCustomary + "] = @inSSF, " +
            "[" + NutriStringConstants.ColumnTSFMetric + "] = @mmTSF, " +
            "[" + NutriStringConstants.ColumnTSFCustomary + "] = @inTSF, " +
            "[" + NutriStringConstants.ColumnMUACMetric + "] = @cmMUAC, " +
            "[" + NutriStringConstants.ColumnMUACCustomary + "] = @inMUAC, " +
            "";

            command = command +
            "[fldCDCHAC] = @CDCHAC, " +
            "[fldCDCWHC] = @CDCWHC, " +
            "[fldCDCWAC] = @CDCWAC, " +
            "[fldCDCHAZ] = @CDCHAZ, " +
            "[fldCDCWHZ] = @CDCWHZ, " +
            "[fldCDCWAZ] = @CDCWAZ, " +
            "[fldCDCBMIC] = @CDCBMIC, " +
            "[fldCDCBMIZ] = @CDCBMIZ, " +
            "[fldCDCHCircumC] = @CDCHCircumC, " +
            "[fldCDCHCircumZ] = @CDCHCircumZ, " +
            "[fldCDCHAFlag] = @CDCHAFlag, " +
            "[fldCDCWAFlag] = @CDCWAFlag, " +
            "[fldCDCWHFlag] = @CDCWHFlag, " +
            "[fldCDCBMIFlag] = @CDCBMIFlag, " +
            "[fldCDCFlag] = @CDCFlag, " +
            "[fldWHO1978HAC] = @WHO1978HAC, " +
            "[fldWHO1978WHC] = @WHO1978WHC, " +
            "[fldWHO1978WAC] = @WHO1978WAC, " +
            "[fldWHO1978HAZ] = @WHO1978HAZ, " +
            "[fldWHO1978WHZ] = @WHO1978WHZ, " +
            "[fldWHO1978WAZ] = @WHO1978WAZ, " +
            "[fldWHO1978HAPM] = @WHO1978HAPM, " +
            "[fldWHO1978WHPM] = @WHO1978WHPM, " +
            "[fldWHO1978WAPM] = @WHO1978WAPM, " +
            "[fldWHO1978Flag] = @WHO1978Flag, " +
            "[fldMUACAgeZ] = @MUACAgeZ, " +
            "[fldMUACHeightZ] = @MUACHeightZ, " +
            "[fldBMI] = @BMI, " +
            "[fldWHO2006HAC] = @WHO2006HAC, " +
            "[fldWHO2006WHC] = @WHO2006WHC, " +
            "[fldWHO2006WAC] = @WHO2006WAC, " +
            "[fldWHO2006HAZ] = @WHO2006HAZ, " +
            "[fldWHO2006WHZ] = @WHO2006WHZ, " +
            "[fldWHO2006WAZ] = @WHO2006WAZ, " +
            "[fldWHO2006SSFC] = @WHO2006SSFC, " +
            "[fldWHO2006TSFC] = @WHO2006TSFC, " +
            "[fldWHO2006HCircumC] = @WHO2006HCircumC, " +
            "[fldWHO2006BMIC] = @WHO2006BMIC, " +
            "[fldWHO2006MUACC] = @WHO2006MUACC, " +
            "[fldWHO2006SSFZ] = @WHO2006SSFZ, " +
            "[fldWHO2006TSFZ] = @WHO2006TSFZ, " +
            "[fldWHO2006HCircumZ] = @WHO2006HCircumZ, " +
            "[fldWHO2006BMIZ] = @WHO2006BMIZ, " +
            "[fldWHO2006MUACZ] = @WHO2006MUACZ, " +
            "[fldWHO2006HAFlag] = @WHO2006HAFlag, " +
            "[fldWHO2006WAFlag] = @WHO2006WAFlag, " +
            "[fldWHO2006WHFlag] = @WHO2006WHFlag, " +
            "[fldWHO2006BMIFlag] = @WHO2006BMIFlag, " +
            "[fldWHO2006HeadFlag] = @WHO2006HeadFlag, " +
            "[fldWHO2006SSFFlag] = @WHO2006SSFFlag, " +
            "[fldWHO2006TSFFlag] = @WHO2006TSFFlag, " +
            "[fldWHO2006MUACFlag] = @WHO2006MUACFlag, " +
                /*
                "[fldWHO2006MMSitting] = @WHO2006MMSitting, " +
                "[fldWHO2006MMStandingAssisted] = @WHO2006MMStandingAssisted, " +
                "[fldWHO2006MMCrawling] = @WHO2006MMCrawling, " +
                "[fldWHO2006MMWalkingAssisted] = @WHO2006MMWalkingAssisted, " +
                "[fldWHO2006MMStanding] = @WHO2006MMStanding, " +
                "[fldWHO2006MMWalking] = @WHO2006MMWalking, " +
                */
            "[fldWHO2007HAC] = @WHO2007HAC, " +
            "[fldWHO2007HAZ] = @WHO2007HAZ, " +
            "[fldWHO2007WAC] = @WHO2007WAC, " +
            "[fldWHO2007WAZ] = @WHO2007WAZ, " +
            "[fldWHO2007BMIC] = @WHO2007BMIC, " +
            "[fldWHO2007BMIZ] = @WHO2007BMIZ, " +
            "[fldWHO2007HAFlag] = @WHO2007HAFlag, " +
            "[fldWHO2007WAFlag] = @WHO2007WAFlag, " +
            "[fldWHO2007BMIFlag] = @WHO2007BMIFlag, " +
            "[fldCreatedDate] = @CreatedDate, " +
            "[fldModifiedDate] = @ModifiedDate, " +
            "[fldModifiedBy] = @ModifiedBy ";

            command = command + "where [" + NutriStringConstants.ColumnID + "] = @Id and [" + NutriStringConstants.ColumnVisitDate + "] = @CurDate";

            updateQuery = db.CreateQuery(command);

            updateQuery.Parameters.Add(new QueryParameter("@CurrentDate", DbType.Date, this.Visit.VisitDate));
            updateQuery.Parameters.Add(new QueryParameter("@FirstName", DbType.String, this.FirstName));
            updateQuery.Parameters.Add(new QueryParameter("@Name", DbType.String, this.LastName));
            updateQuery.Parameters.Add(new QueryParameter("@Sex", DbType.String, this.Gender));
            updateQuery.Parameters.Add(new QueryParameter("@Age", DbType.Single, CheckStringForDatabase(this.Visit.Age)));

            if (this.UseBirthDate == false)
                updateQuery.Parameters.Add(new QueryParameter("@BirthDate", DbType.Date, DBNull.Value));
            else
                updateQuery.Parameters.Add(new QueryParameter("@BirthDate", DbType.Date, this.BirthDate));

            updateQuery.Parameters.Add(new QueryParameter("@Address", DbType.String, this.Address));
            updateQuery.Parameters.Add(new QueryParameter("@Cluster", DbType.String, this.Cluster));
            updateQuery.Parameters.Add(new QueryParameter("@Team", DbType.String, this.Team));
            updateQuery.Parameters.Add(new QueryParameter("@Household", DbType.String, this.Household));
            updateQuery.Parameters.Add(new QueryParameter("@Mother", DbType.String, this.Mother));
            updateQuery.Parameters.Add(new QueryParameter("@Father", DbType.String, this.Father));

            if (this.Visit.Edema)
                updateQuery.Parameters.Add(new QueryParameter("@Edema", DbType.Byte, 1));
            else
                updateQuery.Parameters.Add(new QueryParameter("@Edema", DbType.Byte, 0));

            if (this.Visit.Recumbent)
                updateQuery.Parameters.Add(new QueryParameter("@Recumbent", DbType.Byte, 1));
            else
                updateQuery.Parameters.Add(new QueryParameter("@Recumbent", DbType.Byte, 0));

            updateQuery.Parameters.Add(new QueryParameter("@Notes", DbType.String, this.Visit.Notes));

            updateQuery.Parameters.Add(new QueryParameter("@Cm", DbType.Single, CheckStringForDatabase(this.Visit.HeightMetric)));
            updateQuery.Parameters.Add(new QueryParameter("@Inch", DbType.Single, CheckStringForDatabase(this.Visit.HeightCustomary)));
            updateQuery.Parameters.Add(new QueryParameter("@Kilos", DbType.Single, CheckStringForDatabase(this.Visit.WeightMetric)));
            updateQuery.Parameters.Add(new QueryParameter("@Lbs", DbType.Single, CheckStringForDatabase(this.Visit.WeightCustomary)));
            updateQuery.Parameters.Add(new QueryParameter("@Ozs", DbType.Single, CheckStringForDatabase(this.Visit.WeightOunces)));
            updateQuery.Parameters.Add(new QueryParameter("@cmHCircum", DbType.Single, CheckStringForDatabase(this.Visit.HeadCircumferenceMetric)));
            updateQuery.Parameters.Add(new QueryParameter("@inHCircum", DbType.Single, CheckStringForDatabase(this.Visit.HeadCircumferenceCustomary)));
            updateQuery.Parameters.Add(new QueryParameter("@mmSSF", DbType.Single, CheckStringForDatabase(this.Visit.SubscapularSkinFoldMetric)));
            updateQuery.Parameters.Add(new QueryParameter("@inSSF", DbType.Single, CheckStringForDatabase(this.Visit.SubscapularSkinFoldCustomary)));
            updateQuery.Parameters.Add(new QueryParameter("@mmTSF", DbType.Single, CheckStringForDatabase(this.Visit.TricepsSkinFoldMetric)));
            updateQuery.Parameters.Add(new QueryParameter("@inTSF", DbType.Single, CheckStringForDatabase(this.Visit.TricepsSkinFoldCustomary)));
            updateQuery.Parameters.Add(new QueryParameter("@cmMUAC", DbType.Single, CheckStringForDatabase(this.Visit.ArmCircumferenceMetric)));
            updateQuery.Parameters.Add(new QueryParameter("@inMUAC", DbType.Single, CheckStringForDatabase(this.Visit.ArmCircumferenceCustomary)));
            updateQuery.Parameters.Add(new QueryParameter("@CDCHAC", DbType.Single, CheckStringForDatabase(this.Visit.CDCHAC)));
            updateQuery.Parameters.Add(new QueryParameter("@CDCWHC", DbType.Single, CheckStringForDatabase(this.Visit.CDCWHC)));
            updateQuery.Parameters.Add(new QueryParameter("@CDCWAC", DbType.Single, CheckStringForDatabase(this.Visit.CDCWAC)));
            updateQuery.Parameters.Add(new QueryParameter("@CDCHAZ", DbType.Single, CheckStringForDatabase(this.Visit.CDCHAZ)));
            updateQuery.Parameters.Add(new QueryParameter("@CDCWHZ", DbType.Single, CheckStringForDatabase(this.Visit.CDCWHZ)));
            updateQuery.Parameters.Add(new QueryParameter("@CDCWAZ", DbType.Single, CheckStringForDatabase(this.Visit.CDCWAZ)));
            updateQuery.Parameters.Add(new QueryParameter("@CDCBMIC", DbType.Single, CheckStringForDatabase(this.Visit.CDCBMIC)));
            updateQuery.Parameters.Add(new QueryParameter("@CDCBMIZ", DbType.Single, CheckStringForDatabase(this.Visit.CDCBMIZ)));
            updateQuery.Parameters.Add(new QueryParameter("@CDCHCircumC", DbType.Single, CheckStringForDatabase(this.Visit.CDCHCircumC)));
            updateQuery.Parameters.Add(new QueryParameter("@CDCHCircumZ", DbType.Single, CheckStringForDatabase(this.Visit.CDCHCircumZ)));
            updateQuery.Parameters.Add(new QueryParameter("@CDCHAFlag", DbType.Single, CheckStringForDatabase(this.Visit.CDCHAFlag)));
            updateQuery.Parameters.Add(new QueryParameter("@CDCWAFlag", DbType.Single, CheckStringForDatabase(this.Visit.CDCWAFlag)));
            updateQuery.Parameters.Add(new QueryParameter("@CDCWHFlag", DbType.Single, CheckStringForDatabase(this.Visit.CDCWHFlag)));
            updateQuery.Parameters.Add(new QueryParameter("@CDCBMIFlag", DbType.Single, CheckStringForDatabase(this.Visit.CDCBMIFlag)));

            if (this.Visit.CDCFlag.Equals(NutriStringConstants.Null) || string.IsNullOrEmpty(this.Visit.CDCFlag))
                updateQuery.Parameters.Add(new QueryParameter("@CDCFlag", DbType.Byte, DBNull.Value));
            else
                updateQuery.Parameters.Add(new QueryParameter("@CDCFlag", DbType.Byte, Convert.ToByte(this.Visit.CDCFlag)));

            updateQuery.Parameters.Add(new QueryParameter("@WHO1978HAC", DbType.Single, CheckStringForDatabase(this.Visit.WHO1978HAC)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO1978WHC", DbType.Single, CheckStringForDatabase(this.Visit.WHO1978WHC)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO1978WAC", DbType.Single, CheckStringForDatabase(this.Visit.WHO1978WAC)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO1978HAZ", DbType.Single, CheckStringForDatabase(this.Visit.WHO1978HAZ)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO1978WHZ", DbType.Single, CheckStringForDatabase(this.Visit.WHO1978WHZ)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO1978WAZ", DbType.Single, CheckStringForDatabase(this.Visit.WHO1978WAZ)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO1978HAPM", DbType.Single, CheckStringForDatabase(this.Visit.WHO1978HAPM)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO1978WHPM", DbType.Single, CheckStringForDatabase(this.Visit.WHO1978WHPM)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO1978WAPM", DbType.Single, CheckStringForDatabase(this.Visit.WHO1978WAPM)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO1978Flag", DbType.Single, CheckStringForDatabase(this.Visit.WHO1978Flag)));
            updateQuery.Parameters.Add(new QueryParameter("@MUACAgeZ", DbType.Single, CheckStringForDatabase(this.Visit.MUACAgeZ)));
            updateQuery.Parameters.Add(new QueryParameter("@MUACHeightZ", DbType.Single, CheckStringForDatabase(this.Visit.MUACHeightZ)));
            updateQuery.Parameters.Add(new QueryParameter("@BMI", DbType.Single, CheckStringForDatabase(this.Visit.BMI)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO2006HAC", DbType.Single, CheckStringForDatabase(this.Visit.WHO2006HAC)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO2006WHC", DbType.Single, CheckStringForDatabase(this.Visit.WHO2006WHC)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO2006WAC", DbType.Single, CheckStringForDatabase(this.Visit.WHO2006WAC)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO2006HAZ", DbType.Single, CheckStringForDatabase(this.Visit.WHO2006HAZ)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO2006WHZ", DbType.Single, CheckStringForDatabase(this.Visit.WHO2006WHZ)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO2006WAZ", DbType.Single, CheckStringForDatabase(this.Visit.WHO2006WAZ)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO2006SSFC", DbType.Single, CheckStringForDatabase(this.Visit.WHO2006SSFC)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO2006TSFC", DbType.Single, CheckStringForDatabase(this.Visit.WHO2006TSFC)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO2006HCircumC", DbType.Single, CheckStringForDatabase(this.Visit.WHO2006HCircumC)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO2006BMIC", DbType.Single, CheckStringForDatabase(this.Visit.WHO2006BMIC)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO2006MUACC", DbType.Single, CheckStringForDatabase(this.Visit.WHO2006MUACC)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO2006SSFZ", DbType.Single, CheckStringForDatabase(this.Visit.WHO2006SSFZ)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO2006TSFZ", DbType.Single, CheckStringForDatabase(this.Visit.WHO2006TSFZ)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO2006HCircumZ", DbType.Single, CheckStringForDatabase(this.Visit.WHO2006HCircumZ)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO2006BMIZ", DbType.Single, CheckStringForDatabase(this.Visit.WHO2006BMIZ)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO2006MUACZ", DbType.Single, CheckStringForDatabase(this.Visit.WHO2006MUACZ)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO2006HAFlag", DbType.Single, CheckStringForDatabase(this.Visit.WHO2006HAFlag)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO2006WAFlag", DbType.Single, CheckStringForDatabase(this.Visit.WHO2006WAFlag)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO2006WHFlag", DbType.Single, CheckStringForDatabase(this.Visit.WHO2006WHFlag)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO2006BMIFlag", DbType.Single, CheckStringForDatabase(this.Visit.WHO2006BMIFlag)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO2006HeadFlag", DbType.Single, CheckStringForDatabase(this.Visit.WHO2006HeadFlag)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO2006SSFFlag", DbType.Single, CheckStringForDatabase(this.Visit.WHO2006MUACFlag)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO2006TSFFlag", DbType.Single, CheckStringForDatabase(this.Visit.WHO2006MUACFlag)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO2006MUACFlag", DbType.Single, CheckStringForDatabase(this.Visit.WHO2006MUACFlag)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO2007HAC", DbType.Single, CheckStringForDatabase(this.Visit.WHO2007HAC)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO2007HAZ", DbType.Single, CheckStringForDatabase(this.Visit.WHO2007HAZ)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO2007WAC", DbType.Single, CheckStringForDatabase(this.Visit.WHO2007WAC)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO2007WAZ", DbType.Single, CheckStringForDatabase(this.Visit.WHO2007WAZ)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO2007BMIC", DbType.Single, CheckStringForDatabase(this.Visit.WHO2007BMIC)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO2007BMIZ", DbType.Single, CheckStringForDatabase(this.Visit.WHO2007BMIZ)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO2007HAFlag", DbType.Single, CheckStringForDatabase(this.Visit.WHO2007HAFlag)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO2007WAFlag", DbType.Single, CheckStringForDatabase(this.Visit.WHO2007WAFlag)));
            updateQuery.Parameters.Add(new QueryParameter("@WHO2007BMIFlag", DbType.Single, CheckStringForDatabase(this.Visit.WHO2007BMIFlag)));
            updateQuery.Parameters.Add(new QueryParameter("@CreatedDate", DbType.String, this.Visit.CreationDate));
            updateQuery.Parameters.Add(new QueryParameter("@ModifiedDate", DbType.String, System.DateTime.Now));
            updateQuery.Parameters.Add(new QueryParameter("@ModifiedBy", DbType.String, userName));

            updateQuery.Parameters.Add(new QueryParameter("@Id", DbType.Int32, this.Id));
            updateQuery.Parameters.Add(new QueryParameter("@CurDate", DbType.Date, this.Visit.OldVisitDate));
            
            int x = db.ExecuteNonQuery(updateQuery);

            if (x < 1)
            {
                return false;
            }
            else
            {
                return true;
            }

        } // end SaveData()


        /// <summary>
        /// Creates a new measurement for an existing child. Will insert a new, mostly-blank record into the 
        /// database.
        /// </summary>        
        /// <returns>bool</returns>
        internal bool AddNewMeasurement()
        {
            DateTime DOM = this.Visit.VisitDate;
            string tableName = Table.TableName;

            try
            {                
                dt = db.GetTableData(tableName, "*", "fldIdNo, fldCurrentDate ASC");
                dt.TableName = tableName;

                string userName = System.Security.Principal.WindowsIdentity.GetCurrent().Name.ToString();
                DataRow[] selectedRows = null;
                DateTime DOB = this.BirthDate;
                int id = this.Id;

                selectedRows = dt.Select(NutriStringConstants.ColumnID + " = " + id.ToString() + " AND fldCurrentDate = " + db.FormatDate(DOM), NutriStringConstants.ColumnID + " DESC");

                int records = selectedRows.Length;
                short counter = 0;

                // While the record count we receive is larger than zero (in other words, while
                // duplicates continue to be found), do the following.
                while (records > 0 && counter < Int16.MaxValue)
                {
                    // Add a day to our dateTime object
                    DOM = DOM.Date.AddDays(1);
                    selectedRows = dt.Select(NutriStringConstants.ColumnID + " = " + id.ToString() + " AND " + NutriStringConstants.ColumnVisitDate + " = " + db.FormatDate(DOM), NutriStringConstants.ColumnID + " DESC");
                    records = selectedRows.Length;
                    //MessageBox.Show(records.ToString() + " " + DOM.ToShortDateString());
                    counter++;
                } // end while                
                string sex = "M";
                if (this.Gender.Equals("F"))
                    sex = "F";

                Query insertQuery = db.CreateQuery("insert into " + dt.TableName + "([" + NutriStringConstants.ColumnLastName + "], [" + NutriStringConstants.ColumnFirstName + "], [" + NutriStringConstants.ColumnID + "], [" + NutriStringConstants.ColumnVisitDate + "], [" + NutriStringConstants.ColumnBirthDate + "], [" + NutriStringConstants.ColumnSex + "], [" + NutriStringConstants.ColumnRecumbent + "], [fldCreatedDate], [fldModifiedDate], [fldModifiedBy]) " +
                        "values (@LastName, @FirstName, @Id, @CurrentDate, @BirthDate, @Sex, @Recumbent, @CreatedDate, @ModifiedDate, @ModifiedBy)");
                insertQuery.Parameters.Add(new QueryParameter("@LastName", DbType.String, this.LastName));
                insertQuery.Parameters.Add(new QueryParameter("@FirstName", DbType.String, this.FirstName));
                insertQuery.Parameters.Add(new QueryParameter("@Id", DbType.Int32, id));
                insertQuery.Parameters.Add(new QueryParameter("@CurrentDate", DbType.DateTime, DOM));

                if (!this.UseBirthDate)
                    insertQuery.Parameters.Add(new QueryParameter("@BirthDate", DbType.DateTime, DBNull.Value));
                else
                    insertQuery.Parameters.Add(new QueryParameter("@BirthDate", DbType.DateTime, DOB));

                insertQuery.Parameters.Add(new QueryParameter("@Sex", DbType.String, sex));
                insertQuery.Parameters.Add(new QueryParameter("@Recumbent", DbType.Int32, 0));
                insertQuery.Parameters.Add(new QueryParameter("@CreatedDate", DbType.String, System.DateTime.Now));
                insertQuery.Parameters.Add(new QueryParameter("@ModifiedDate", DbType.String, System.DateTime.Now));
                insertQuery.Parameters.Add(new QueryParameter("@ModifiedBy", DbType.String, userName));

                db.ExecuteNonQuery(insertQuery);
            }
            catch (Exception ex)
            {
                MessageBox.Show("There was a problem creating a new record: " + ex.ToString());
                return false;
            }

            dt = db.GetTableData(tableName, "*", "" + NutriStringConstants.ColumnID + ", " + NutriStringConstants.ColumnVisitDate + " ASC");
            dt.TableName = tableName;

            LoadValuesFromDatabase(false, false, this.Id, true, DOM, true);

            return true;
        }

        /// <summary>
        /// Creates a new patient. Will insert a new, mostly-blank record into the database.
        /// </summary>        
        /// <returns>bool</returns>
        internal bool AddNewID(bool firstRecord)
        {
            int id = 100;

            try
            {
                string userName = System.Security.Principal.WindowsIdentity.GetCurrent().Name.ToString();
                Query selectQuery;

                if (!firstRecord)
                {
                    selectQuery = db.CreateQuery("select max(fldIdNo) from " + dt.TableName);// + " order by fldIdNo DESC");
                    id = (int)db.ExecuteScalar(selectQuery) + 1;
                }

                DateTime DOB = DateTime.Today.Subtract(TimeSpan.FromDays(60));
                DateTime DOM = DateTime.Today;

                Query insertQuery = db.CreateQuery("insert into " + dt.TableName + "([" + NutriStringConstants.ColumnLastName + "], [" + NutriStringConstants.ColumnFirstName + "], [" + NutriStringConstants.ColumnID + "], [" + NutriStringConstants.ColumnVisitDate + "], [" + NutriStringConstants.ColumnBirthDate + "], [" + NutriStringConstants.ColumnSex + "], [" + NutriStringConstants.ColumnRecumbent + "], [fldCreatedDate], [fldModifiedDate], [fldModifiedBy]) " +
                        "values (@LastName, @FirstName, @Id, @CurrentDate, @BirthDate, @Sex, @Recumbent, @CreatedDate, @ModifiedDate, @ModifiedBy)");
                insertQuery.Parameters.Add(new QueryParameter("@LastName", DbType.String, ""));
                insertQuery.Parameters.Add(new QueryParameter("@FirstName", DbType.String, ""));
                insertQuery.Parameters.Add(new QueryParameter("@Id", DbType.Int32, id));
                insertQuery.Parameters.Add(new QueryParameter("@CurrentDate", DbType.DateTime, DOM));
                insertQuery.Parameters.Add(new QueryParameter("@BirthDate", DbType.DateTime, DBNull.Value));
                insertQuery.Parameters.Add(new QueryParameter("@Sex", DbType.String, "M"));
                insertQuery.Parameters.Add(new QueryParameter("@Recumbent", DbType.Int32, 0));
                insertQuery.Parameters.Add(new QueryParameter("@CreatedDate", DbType.String, System.DateTime.Now));
                insertQuery.Parameters.Add(new QueryParameter("@ModifiedDate", DbType.String, System.DateTime.Now));
                insertQuery.Parameters.Add(new QueryParameter("@ModifiedBy", DbType.String, userName));

                db.ExecuteNonQuery(insertQuery);
            }
            catch (Exception ex)
            {
                MessageBox.Show("There was a problem creating a new record: " + ex.ToString());
                return false;
            }

            LoadValuesFromDatabase(false, false, id, true, DateTime.Today, false);

            return true;
        } // end AddNewID()     


        /// <summary>
        /// Loads the first record in the database.
        /// </summary>        
        internal bool LoadFirstRecord()
        {
            Query selectQuery = db.CreateQuery("select MIN(fldIdNo) from " + dt.TableName);
            int firstId = (int)db.ExecuteScalar(selectQuery);
            LoadValuesFromDatabase(true, true, firstId, true, DateTime.Today, false);
            return true;
        }

        /// <summary>
        /// Loads the last record in the database.
        /// </summary>
        internal bool LoadLastRecord()
        {
            Query selectQuery = db.CreateQuery("select MAX(fldIdNo) from " + dt.TableName);
            int lastId = (int)db.ExecuteScalar(selectQuery);
            LoadValuesFromDatabase(true, true, lastId, true, DateTime.Today, false);
            return true;
        }

        /// <summary>
        /// Checks the table to see if more than one visit exists for the current ID number.
        /// </summary>
        internal bool HasMultipleVisits()
        {
            Query selectQuery = db.CreateQuery("select * from " + dt.TableName + " where fldIdNo = " + this.Id.ToString());
            int count = 0;
            IDataReader reader = db.ExecuteReader(selectQuery);
            while (reader.Read())
            {
                count++;
                if (count > 1)
                    return true;
            }
            return false;
        }

        /// <summary>
        /// Deletes the current values from the patient object
        /// </summary>        
        internal bool DeleteRecord()
        {
            // If we don't have a database open, stop
            if (DatabaseLoaded == false)
                return false;

            try
            {
                int idNo = this.Id;
                Query deleteQuery = Database.CreateQuery("delete * from " + Table.TableName + " where [" + NutriStringConstants.ColumnID + "] = @Id AND " + NutriStringConstants.ColumnVisitDate + " = @CurrentDate");
                deleteQuery.Parameters.Add(new QueryParameter("@Id", DbType.Int32, idNo));
                deleteQuery.Parameters.Add(new QueryParameter("@CurrentDate", DbType.DateTime, this.Visit.VisitDate));
                db.ExecuteNonQuery(deleteQuery);

                DataRow[] selectedRows = Table.Select("fldIdNo = " + this.Id);
                bool partial = false;
                if (selectedRows.Length > 0)
                    partial = true;

                ResetConnection();
                // This checks to see if we have a record. If we do, then go back one date of measurement
                if (partial)
                {
                    this.LoadValuesFromDatabase(false, false, this.Id, false, this.Visit.VisitDate, false);


                }
                // Otherwise, we need to load a new ID number
                else
                {
                    this.LoadValuesFromDatabase(false, true, this.Id, false, this.Visit.VisitDate, false);
                }

                // TODO: This doesn't work properly if the user deletes the last record for the first ID in the
                // database. We need a way to account for this. It's not a big deal now, and it won't (?) crash
                // the program, but it'll be nice to have.                
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                return false;
            }
            return true;
        } // end DeleteRecord

        /// <summary>
        /// Loads values from the database into the Patient object.
        /// </summary>
        /// <param name="right">If true, indicates forward movement in the record count. If false, indicates backwards movement.</param>
        /// <param name="full">If true, indicates a full refresh is needed (record buttons were used). If false, indicates only the date of measurement has been cycled.</param>
        /// <param name="id">The ID number to load. Is ignored unless goToId is used.</param>
        /// <param name="goToId">If true, ignores the right/full parameters and uses the ID number.</param>
        /// <param name="date">The specific date of measurement to load for a given ID. If useDate is not specified, this parameter is ignored.</param>
        /// <param name="useDate">If true, loads a specific date for a given ID number.</param>
        /// <returns>bool</returns>
        internal bool LoadValuesFromDatabase(bool right, bool full, int id, bool goToId, DateTime date, bool useDate)
        {
            #region Input Validation
            // Input validation
            try
            {
                if (id <= 0)
                {
                    return false;
                }
            }
            catch (Exception)
            {
                return false;
            }
            #endregion

            IDataReader selectedRow = null;

            if (goToId && !useDate)
            {
                Query selectQuery = db.CreateQuery("select * from " + Table.TableName + " where " + NutriStringConstants.ColumnID + " = " + id.ToString());
                selectedRow = db.ExecuteReader(selectQuery);
                if (!selectedRow.Read())
                {
                    return false;
                }

            }
            else if (goToId && useDate)
            {
                string DOM = String.Format("{0:G}", date.ToString());
                DOM = db.FormatDate(date);
                //MessageBox.Show(DOM.ToString());
                Query selectQuery = db.CreateQuery("select * from " + Table.TableName + " where " + NutriStringConstants.ColumnID + " = " + id.ToString() + " AND " + NutriStringConstants.ColumnVisitDate + " = " + DOM);
                selectedRow = db.ExecuteReader(selectQuery);
                if (!selectedRow.Read())
                {
                    return false;
                }

            }
            else if (full && right)
            {
                string query = "select min(" + NutriStringConstants.ColumnID + ") from " + Table.TableName + " where " + NutriStringConstants.ColumnID + " > " + id.ToString();

                Query selectQuery = db.CreateQuery(query);

                object result = db.ExecuteScalar(selectQuery);
                if (result != DBNull.Value)
                {
                    selectQuery = db.CreateQuery("select * from " + Table.TableName + " where " + NutriStringConstants.ColumnID + " = " + result.ToString());
                    selectedRow = db.ExecuteReader(selectQuery);
                    if (!selectedRow.Read())
                    {
                        return false;
                    }
                }
                else
                {
                    return false;
                }
            }
            else if (full && !right)
            {
                string query = "select max(fldIdNo) from " + Table.TableName + " where fldIdNo < " + id.ToString();

                Query selectQuery = db.CreateQuery(query);

                object result = db.ExecuteScalar(selectQuery);
                if (result != DBNull.Value)
                {
                    selectQuery = db.CreateQuery("select * from " + Table.TableName + " where fldIdNo = " + result.ToString());
                    selectedRow = db.ExecuteReader(selectQuery);
                    if (!selectedRow.Read())
                    {
                        return false;
                    }
                }
                else
                {
                    return false;
                }
            }

            else if (!full && right)
            {

                string DOM = String.Format("{0:G}", date.ToString());
                DOM = db.FormatDate(date);

                Query selectQuery = db.CreateQuery("select * from " + Table.TableName + " where fldIdNo = " + id.ToString() + " AND " + NutriStringConstants.ColumnVisitDate + " > " + DOM + " order by " + NutriStringConstants.ColumnVisitDate + " ASC");

                selectedRow = db.ExecuteReader(selectQuery);
                if (!selectedRow.Read())
                {
                    return false;
                }
            }
            else if (!full && !right)
            {
                string DOM = String.Format("{0:G}", date.ToString());
                DOM = db.FormatDate(date);

                Query selectQuery = db.CreateQuery("select * from " + Table.TableName + " where fldIdNo = " + id.ToString() + " AND " + NutriStringConstants.ColumnVisitDate + " < " + DOM + " order by " + NutriStringConstants.ColumnVisitDate + " DESC");
                selectedRow = db.ExecuteReader(selectQuery);
                if (!selectedRow.Read())
                {
                    return false;
                }
            }

            this.Id = Convert.ToInt32(selectedRow[NutriStringConstants.ColumnID]);
            this.Cluster = selectedRow[NutriStringConstants.ColumnCluster].ToString();
            this.Team = selectedRow[NutriStringConstants.ColumnTeam].ToString();
            this.Household = selectedRow[NutriStringConstants.ColumnHousehold].ToString();
            this.FirstName = selectedRow[NutriStringConstants.ColumnFirstName].ToString();
            this.LastName = selectedRow[NutriStringConstants.ColumnLastName].ToString();

            string tempDOB = selectedRow[NutriStringConstants.ColumnBirthDate].ToString();

            if (string.IsNullOrEmpty(tempDOB))
            {
                this.UseBirthDate = false;
            }
            else
            {
                this.UseBirthDate = true;
                this.BirthDate = Convert.ToDateTime(tempDOB);
            }

            string age = selectedRow[NutriStringConstants.ColumnAge].ToString();
            this.Visit.Age = age;

            // Date Time stuff.... ugh
            string tempDateStr = selectedRow["fldCurrentDate"].ToString();
            DateTime tempDate = Convert.ToDateTime(tempDateStr);
            if (tempDate > DateTimePicker.MaximumDateTime || tempDate < DateTimePicker.MinimumDateTime)
            {
                DateTime date1 = new DateTime(1753, 12, 12, 12, 0, 0);
                this.Visit.VisitDate = date1;
                this.Visit.OldVisitDate = date1;
            }
            else
            {
                this.Visit.VisitDate = Convert.ToDateTime(tempDateStr);
                this.Visit.OldVisitDate = this.Visit.VisitDate;
            }

            // end date time section

            try
            {
                this.Visit.CreationDate = Convert.ToDateTime(selectedRow["fldCreatedDate"]);
            }
            catch
            {
            }

            try
            {
                this.Visit.ModifiedDate = Convert.ToDateTime(selectedRow["fldModifiedDate"]);
            }
            catch
            {
            }

            this.Visit.HeightCustomary = String.Format("{0:0.00}", selectedRow[NutriStringConstants.ColumnHeightCustomary].ToString());
            this.Visit.WeightCustomary = String.Format("{0:0.00}", selectedRow[NutriStringConstants.ColumnWeightCustomary].ToString());
            this.Visit.ArmCircumferenceCustomary = String.Format("{0:0.00}", selectedRow[NutriStringConstants.ColumnMUACCustomary].ToString());
            this.Visit.HeadCircumferenceCustomary = String.Format("{0:0.00}", selectedRow[NutriStringConstants.ColumnHeadCustomary].ToString());
            this.Visit.SubscapularSkinFoldCustomary = String.Format("{0:0.00}", selectedRow[NutriStringConstants.ColumnSSFCustomary].ToString());
            this.Visit.TricepsSkinFoldCustomary = String.Format("{0:0.00}", selectedRow[NutriStringConstants.ColumnTSFCustomary].ToString());

            this.Visit.HeightMetric = String.Format("{0:0.00}", selectedRow[NutriStringConstants.ColumnHeightMetric].ToString());
            this.Visit.WeightMetric = String.Format("{0:0.00}", selectedRow[NutriStringConstants.ColumnWeightMetric].ToString());
            this.Visit.ArmCircumferenceMetric = String.Format("{0:0.00}", selectedRow[NutriStringConstants.ColumnMUACMetric].ToString());
            this.Visit.HeadCircumferenceMetric = String.Format("{0:0.00}", selectedRow[NutriStringConstants.ColumnHeadMetric].ToString());
            this.Visit.SubscapularSkinFoldMetric = String.Format("{0:0.00}", selectedRow[NutriStringConstants.ColumnSSFMetric].ToString());
            this.Visit.TricepsSkinFoldMetric = String.Format("{0:0.00}", selectedRow[NutriStringConstants.ColumnTSFMetric].ToString());

            this.Visit.Notes = selectedRow[NutriStringConstants.ColumnNotes].ToString();

            if (selectedRow[NutriStringConstants.ColumnSex].ToString().Equals("F"))
            {
                this.Gender = "F";
            }
            else
            {
                this.Gender = "M";
            }

            if (selectedRow[NutriStringConstants.ColumnEdema].ToString().Equals("1"))
            {
                this.Visit.Edema = true;
            }
            else
            {
                this.Visit.Edema = false;
            }

            if (selectedRow[NutriStringConstants.ColumnRecumbent].ToString().Equals("1"))
            {
                this.Visit.Recumbent = true;
            }
            else
            {
                this.Visit.Recumbent = false;
            }

            string BMI = selectedRow["fldBMI"].ToString();
            this.Visit.BMI = BMI;
            this.Visit.CDCHCircumC = selectedRow["fldCDCHCircumC"].ToString();
            this.Visit.CDCHCircumZ = selectedRow["fldCDCHCircumZ"].ToString();

            this.Visit.CDCBMIC = selectedRow["fldCDCBMIC"].ToString();
            this.Visit.CDCBMIZ = selectedRow["fldCDCBMIZ"].ToString();

            this.Visit.CDCHAC = selectedRow["fldCDCHAC"].ToString();
            this.Visit.CDCHAZ = selectedRow["fldCDCHAZ"].ToString();

            this.Visit.CDCWAC = selectedRow["fldCDCWAC"].ToString();
            this.Visit.CDCWAZ = selectedRow["fldCDCWAZ"].ToString();

            this.Visit.CDCWHC = selectedRow["fldCDCWHC"].ToString();
            this.Visit.CDCWHZ = selectedRow["fldCDCWHZ"].ToString();

            this.Visit.CDCFlag = selectedRow["fldCDCFlag"].ToString();

            this.Visit.CDCBMIFlag = selectedRow["fldCDCBMIFlag"].ToString();
            this.Visit.CDCHAFlag = selectedRow["fldCDCHAFlag"].ToString();
            this.Visit.CDCWAFlag = selectedRow["fldCDCWAFlag"].ToString();
            this.Visit.CDCWHFlag = selectedRow["fldCDCWHFlag"].ToString();
            this.Visit.WHO1978Flag = selectedRow["fldWHO1978Flag"].ToString();
            this.Visit.WHO1978HAC = selectedRow["fldWHO1978HAC"].ToString();
            this.Visit.WHO1978WHC = selectedRow["fldWHO1978WHC"].ToString();
            this.Visit.WHO1978WAC = selectedRow["fldWHO1978WAC"].ToString();
            this.Visit.WHO1978HAZ = selectedRow["fldWHO1978HAZ"].ToString();
            this.Visit.WHO1978WHZ = selectedRow["fldWHO1978WHZ"].ToString();
            this.Visit.WHO1978WAZ = selectedRow["fldWHO1978WAZ"].ToString();
            this.Visit.WHO1978HAPM = selectedRow["fldWHO1978HAPM"].ToString();
            this.Visit.WHO1978WHPM = selectedRow["fldWHO1978WHPM"].ToString();
            this.Visit.WHO1978WAPM = selectedRow["fldWHO1978WAPM"].ToString();

            this.Visit.WHO2006HAC = selectedRow["fldWHO2006HAC"].ToString();
            this.Visit.WHO2006WHC = selectedRow["fldWHO2006WHC"].ToString();
            this.Visit.WHO2006WAC = selectedRow["fldWHO2006WAC"].ToString();
            this.Visit.WHO2006HAZ = selectedRow["fldWHO2006HAZ"].ToString();
            this.Visit.WHO2006WHZ = selectedRow["fldWHO2006WHZ"].ToString();
            this.Visit.WHO2006WAZ = selectedRow["fldWHO2006WAZ"].ToString();
            this.Visit.WHO2006SSFC = selectedRow["fldWHO2006SSFC"].ToString();
            this.Visit.WHO2006TSFC = selectedRow["fldWHO2006TSFC"].ToString();
            this.Visit.WHO2006HCircumC = selectedRow["fldWHO2006HCircumC"].ToString();
            this.Visit.WHO2006BMIC = selectedRow["fldWHO2006BMIC"].ToString();
            this.Visit.WHO2006MUACC = selectedRow["fldWHO2006MUACC"].ToString();

            this.Visit.WHO2006SSFZ = selectedRow["fldWHO2006SSFZ"].ToString();
            this.Visit.WHO2006TSFZ = selectedRow["fldWHO2006TSFZ"].ToString();
            this.Visit.WHO2006HCircumZ = selectedRow["fldWHO2006HCircumZ"].ToString();
            this.Visit.WHO2006BMIZ = selectedRow["fldWHO2006BMIZ"].ToString();

            this.Visit.WHO2006MUACZ = selectedRow["fldWHO2006MUACZ"].ToString();
            this.Visit.WHO2006HAFlag = selectedRow["fldWHO2006HAFlag"].ToString();
            this.Visit.WHO2006WAFlag = selectedRow["fldWHO2006WAFlag"].ToString();

            this.Visit.WHO2006WHFlag = selectedRow["fldWHO2006WHFlag"].ToString();
            this.Visit.WHO2006BMIFlag = selectedRow["fldWHO2006BMIFlag"].ToString();
            this.Visit.WHO2006HeadFlag = selectedRow["fldWHO2006HeadFlag"].ToString();

            this.Visit.WHO2006SSFFlag = selectedRow["fldWHO2006SSFFlag"].ToString();
            this.Visit.WHO2006TSFFlag = selectedRow["fldWHO2006TSFFlag"].ToString();
            this.Visit.WHO2006MUACFlag = selectedRow["fldWHO2006MUACFlag"].ToString();

            this.Visit.WHO2007HAC = selectedRow["fldWHO2007HAC"].ToString();
            this.Visit.WHO2007HAZ = selectedRow["fldWHO2007HAZ"].ToString();
            this.Visit.WHO2007WAC = selectedRow["fldWHO2007WAC"].ToString();
            this.Visit.WHO2007WAZ = selectedRow["fldWHO2007WAZ"].ToString();
            this.Visit.WHO2007BMIC = selectedRow["fldWHO2007BMIC"].ToString();
            this.Visit.WHO2007BMIZ = selectedRow["fldWHO2007BMIZ"].ToString();
            this.Visit.WHO2007HAFlag = selectedRow["fldWHO2007HAFlag"].ToString();
            this.Visit.WHO2007WAFlag = selectedRow["fldWHO2007WAFlag"].ToString();
            this.Visit.WHO2007BMIFlag = selectedRow["fldWHO2007BMIFlag"].ToString();

            selectedRow.Close();
            selectedRow.Dispose();

            SetFirstOrLast();

            return true;

        } // end LoadValuesFromDatabase()
        #endregion Public Methods

        #region Protected Methods
        #endregion Protected Methods

        #region Private Methods
        /// <summary>
        /// This is specifically needed for UPDATE query. If the input
        /// string is not valid (e.g. empty, has "Undefined" in it, or something like
        /// that), then we must return a DBNull value.
        /// </summary>
        /// <param name="value">The input to check</param>
        /// <returns>object</returns>
        private object CheckStringForDatabase(string value)
        {
            #region Input Validation
            // Input validation            
            try
            {
                double temp = Convert.ToSingle(value);
                if (temp <= -999)
                {
                    return DBNull.Value;
                }
                if (temp >= 99.99999)
                {
                    temp = 99.99999;
                }
            }
            catch
            {
                return DBNull.Value;
            }
            #endregion

            return Convert.ToSingle(value);
        } // end CheckStringForDatabase()

        /// <summary>
        /// Resets the connection so the data table is updated
        /// </summary>                
        private void ResetConnection()
        {
            string tableName = Table.TableName;
            dt = db.GetTableData(tableName, "*", "fldIdNo, fldCurrentDate ASC");
            dt.TableName = tableName;
        }

        /// <summary>
        /// Determines if the record that has been loaded is the first or last record in the data table and then
        /// sets the 'First' and 'Last' values appropriately.
        /// </summary>
        private void SetFirstOrLast()
        {
            IDataReader selectedRow = null;
            // see if this is the first or last ID in the table
            Query selectQuery = db.CreateQuery("select * from " + Table.TableName + " where fldIdNo > " + this.Id.ToString());
            selectedRow = db.ExecuteReader(selectQuery);
            if (!selectedRow.Read())
            {
                this.IsLastId = true;
            }
            else
            {
                this.IsLastId = false;
            }

            selectQuery = db.CreateQuery("select * from " + Table.TableName + " where fldIdNo < " + this.Id.ToString());
            selectedRow = db.ExecuteReader(selectQuery);
            if (!selectedRow.Read())
            {
                this.IsFirstId = true;
            }
            else
            {
                this.IsFirstId = false;
            }
        }
        #endregion Private Methods 
        #endregion Methods
    }
}
